package start;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcCallOperations;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;
    private SimpleJdbcCall procReadActor;
    private SimpleJdbcCall funcGetActorName;
    private SimpleJdbcCall procReadAllActors;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        //this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("t_actor");
        this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("t_actor").usingGeneratedKeyColumns("id");
        this.procReadActor =
                new SimpleJdbcCall(dataSource).withProcedureName("read_actor").withoutProcedureColumnMetaDataAccess().useInParameterNames("in_id").declareParameters(new SqlParameter("in_id",
                                                                                                                                                                                      Types.NUMERIC),
                                                                                                                                                                     new SqlOutParameter("out_first_name",
                                                                                                                                                                                         Types.VARCHAR),
                                                                                                                                                                     new SqlOutParameter("out_last_name",
                                                                                                                                                                                         Types.VARCHAR),
                                                                                                                                                                     new SqlOutParameter("out_birth_date",
                                                                                                                                                                                         Types.DATE));
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.funcGetActorName = new SimpleJdbcCall(jdbcTemplate).withFunctionName("get_actor_name");
        this.procReadAllActors =
                new SimpleJdbcCall(jdbcTemplate).withProcedureName("read_all_actors").returningResultSet("actors",
                                                                                                         ParameterizedBeanPropertyRowMapper.newInstance(Actor.class));
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(3);
        parameters.put("id", actor.getId());
        parameters.put("first_name", actor.getFirstname());
        parameters.put("last_name", actor.getLastname());
        insertActor.execute(parameters);
    }

    public void add2(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstname());
        parameters.put("last_name", actor.getLastname());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    public Actor readActor(Long id) {
        SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
        Map out = procReadActor.execute(in);
        Actor actor = new Actor();
        actor.setId(id);
        actor.setFirstname((String)out.get("out_first_name"));
        actor.setLastname((String)out.get("out_last_name"));
        actor.setBirthDate((Date)out.get("out_birth_date"));
        return actor;
    }

    public String getActorName(Long id) {
        SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
        String name = funcGetActorName.executeFunction(String.class, in);
        return name;
    }

    public List getActorsList() {
        Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
        return (List)m.get("actors");
    }
}
